from typing import Optional

from fastapi import APIRouter, Query, Depends
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db

app_phone_router = APIRouter(prefix="/phone", tags=["phone"])


class AlarmQueryParams(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fpointNo: str = Field("", description="点位编号")
    ftakedateStart: str = Field("", description="开始时间(yyyyMMdd)")
    ftakedateEnd: str = Field("", description="结束时间(yyyyMMdd)")
    fwarringtype: str = Field("0", description="报警类型，默认0(全部)")
    fuserid: str = Field("", description="用户ID")
    fequipmenttype: str = Field("0", description="设备类型")
    fprocflag: str = Field("", description="处理状态")


class AlarmDataItem(BaseModel):
    """报警数据响应条目"""
    fwarringtype: int = Field(..., description="报警类型")
    fwarringid: str = Field(..., description="报警ID")
    fwarringtime: str = Field(..., description="报警时间(yyyy-MM-ddTHH:mm:ss)")
    fwarringnote: str = Field(..., description="报警备注")
    flevelscre: Optional[str] = Field(None, description="危险等级分数")
    fsiteName: str = Field(..., description="站点名称")
    fpointName: str = Field(..., description="点位名称")
    fprocflag: str = Field(..., description="处理状态")
    fmacno: Optional[str] = Field(None, description="设备MAC地址")
    fequipmenttag: Optional[str] = Field(None, description="设备标签")


@app_phone_router.get("/getAlarmInfo2", summary="app报警数据")
def do_get_alarm_info(query: AlarmQueryParams = Query(), db: Session = Depends(get_db)):
    sql = ""
    param = {}
    if int(query.fwarringtype) == 1:
        sql = "select 1 fwarringtype,a.feuipmentid,a.fwarringno fwarringid,a.fpoint_no,a.fequipmentbarcode"
        sql = sql + ",a.fdescribe fwarringnote,a.fgetdate fwarringtime,d.flevelno,e.flevelscre, x.fsite_name"
        sql = sql + ", p.fpoint_name, v.fequipmenttag,a.fprocflag,v.fmacno"
        sql = sql + " from a_tc_equipwarring a,a_tc_equipwarring_user b,s_ts_equipment_dangerset d,s_ts_danger_level e"
        sql = sql + " , a_ts_point p, s_tc_equipment_inv v, s_ts_equipment_info o, a_ts_site x"
        sql = sql + " where 1=1"
        if query.fuserid is not None:
            sql = sql + " and b.fuserno= :fuserid "
            param["fuserid"] = query.fuserid
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no= :fsiteNo "
            param["fsiteNo"] = query.fsiteNo
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
            param["fpointNo"] = query.fpointNo
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(a.fgetdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
            param["ftakedateStart"] = query.ftakedateStart
            param["ftakedateEnd"] = query.ftakedateEnd
        if query.fequipmenttype != "0":
            sql = sql + " and o.fequipmenttype= :fequipmenttype "
            param["fequipmenttype"] = query.fequipmenttype
        if query.fprocflag is not None:
            sql = sql + " and a.fprocflag= :fprocflag "
            param["fprocflag"] = query.fprocflag
        sql = sql + " and a.fsite_no=x.fsite_no"
        sql = sql + " and b.fwarringno=a.fwarringno"
        sql = sql + " and a.feuipmentid=d.feuipmentid and a.ferrorno=d.fdangerno"
        sql = sql + " and d.flevelno=e.flevelno and a.fpoint_no = p.fpoint_no "
        sql = sql + " and a.fequipmentbarcode = v.fequipmentbarcode"
        sql = sql + " and a.feuipmentid=o.feuipmentid"
        sql = sql + " and COALESCE(cast(b.freadtime as varchar),'未阅')='未阅'"
        sql = sql + " order by fwarringtime desc"

    if int(query.fwarringtype) == 2:
        sql = "select 2 fwarringtype,g.fwarringno fwarringid, g.fgetdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + " ,x.fsite_name, p.fpoint_name,g.fprocflag"
        sql = sql + " from a_tc_airwarring g, a_ts_airwarring_user u, a_ts_site x, a_ts_point p"
        sql = sql + " where 1=1"
        if query.fuserid is not None:
            sql = sql + " and u.fuserno= :fuserid "
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no= :fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        if query.fprocflag is not None:
            sql = sql + " and g.fprocflag= :fprocflag "
        sql = sql + " and g.fsite_no=x.fsite_no and g.fpoint_no=p.fpoint_no"
        sql = sql + " and g.fwarringno = u.fwarringno"
        sql = sql + " order by fwarringtime desc"

    if int(query.fwarringtype) == 3:
        sql = "select a.fwarringtype,a.fwarringid, a.fwarringtime, a.fwarringnote"
        sql = sql + " ,a.fsite_name, p.fpoint_name, a.fprocflag"
        sql = sql + " from"
        sql = sql + " (select 3 fwarringtype,g.fwarringno fwarringid, g.fgetdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + " ,x.fsite_name, g.fpoint_no, 2 fprocflag"
        sql = sql + " from s_tc_smartwarring g, s_ts_smartwarring_user u, a_ts_site x"
        sql = sql + " where 1=1"
        if query.fuserid is not None:
            sql = sql + " and u.fuserno=:fuserid "
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and g.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        sql = sql + " and g.fsite_no=x.fsite_no"
        sql = sql + " and g.fwarringno = u.fwarringno) a"
        sql = sql + " left join a_ts_point p on a.fpoint_no=p.fpoint_no"
        sql = sql + " order by a.fwarringtime desc"

    if int(query.fwarringtype) == 4:
        sql = "select 4 fwarringtype,g.fentryid fwarringid, g.fbegdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + ",x.fsite_name, p.fpoint_name,g.fprocflag,g.fmacno, v.fequipmenttag"
        sql = sql + " from a_tc_switchwarring g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        if query.fprocflag is not None:
            sql = sql + " and g.fprocflag= :fprocflag "
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.fmacno = v.fmacno"
        sql = sql + " order by fwarringtime desc"

    if int(query.fwarringtype) == 5:
        sql = "select 5 fwarringtype,g.id fwarringid, g.fgetdate fwarringtime"
        sql = sql + " , case g.devtype when '1' then '门磁探测器报警' when '2' then '水浸报警' when '3' then '烟感报警' when '4' then '燃气报警' "
        sql = sql + " when '5' then '红外感应报警' when '7' then '紧急按钮报警' else '' end fwarringnote"
        sql = sql + " ,x.fsite_name, p.fpoint_name, 2 fprocflag,g.equipment_id fmacno, v.fequipmenttag"
        sql = sql + " from ty_reported_data g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.equipment_id = v.fmacno"
        sql = sql + " and g.status in('8','6')"
        sql = sql + " and g.devtype in ('3','2')"
        sql = sql + " order by fwarringtime desc"

    if int(query.fwarringtype) == 6:
        sql = "select 6 fwarringtype,g.id fwarringid, g.fgetdate fwarringtime"
        sql = sql + " , case g.devtype when '1' then '门磁探测器报警' when '2' then '水浸报警' when '3' then '烟感报警' when '4' then '燃气报警'"
        sql = sql + " when '5' then '红外感应报警' when '7' then '紧急按钮报警' else '' end fwarringnote"
        sql = sql + ",x.fsite_name, p.fpoint_name, 2 fprocflag,g.equipment_id fmacno, v.fequipmenttag"
        sql = sql + " from ty_reported_data g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.equipment_id = v.fmacno"
        sql = sql + " and g.status = '8'"
        sql = sql + " and g.devtype in ('5')"
        sql = sql + " order by fwarringtime desc"

    if int(query.fwarringtype) == 0:
        sql = "select 2 fwarringtype,g.fwarringno fwarringid, g.fgetdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + ",x.fsite_name, p.fpoint_name,g.fprocflag, '' fmacno, '' fequipmenttag"
        sql = sql + " from a_tc_airwarring g, a_ts_airwarring_user u, a_ts_site x, a_ts_point p"
        sql = sql + " where 1=1"
        if query.fuserid is not None:
            sql = sql + " and u.fuserno=:fuserid "
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        if query.fprocflag is not None:
            sql = sql + " and g.fprocflag= :fprocflag "
        sql = sql + " and g.fsite_no=x.fsite_no and g.fpoint_no=p.fpoint_no"
        sql = sql + " and g.fwarringno = u.fwarringno"
        sql = sql + " UNION"
        sql = sql + " select a.fwarringtype,a.fwarringid, a.fwarringtime, a.fwarringnote"
        sql = sql + " ,a.fsite_name, p.fpoint_name, a.fprocflag, '' fmacno, '' fequipmenttag"
        sql = sql + " from"
        sql = sql + " (select 3 fwarringtype,g.fwarringno fwarringid, g.fgetdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + " ,x.fsite_name, g.fpoint_no, 2 fprocflag"
        sql = sql + " from s_tc_smartwarring g, s_ts_smartwarring_user u, a_ts_site x"
        sql = sql + " where 1=1"
        if query.fuserid is not None:
            sql = sql + " and u.fuserno=:fuserid "
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        if query.fprocflag is not None:
            sql = sql + " and g.fprocflag= :fprocflag "
        sql = sql + " and g.fsite_no=x.fsite_no"
        sql = sql + " and g.fwarringno = u.fwarringno) a"
        sql = sql + " left join a_ts_point p on a.fpoint_no=p.fpoint_no"
        sql = sql + " UNION"
        sql = sql + " select 4 fwarringtype,g.fentryid fwarringid, g.fbegdate fwarringtime, g.fdescribe fwarringnote"
        sql = sql + " ,x.fsite_name, p.fpoint_name,g.fprocflag,g.fmacno, v.fequipmenttag"
        sql = sql + " from a_tc_switchwarring g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fbegdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        if query.fprocflag is not None:
            sql = sql + " and g.fprocflag= :fprocflag "
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.fmacno = v.fmacno"
        sql = sql + " UNION"
        sql = sql + " select 5 fwarringtype,g.id fwarringid, g.fgetdate fwarringtime"
        sql = sql + " , case g.devtype when '1' then '门磁探测器报警' when '2' then '水浸报警' when '3' then '烟感报警' when '4' then '燃气报警'"
        sql = sql + " when '5' then '红外感应报警' when '7' then '紧急按钮报警' else '' end fwarringnote"
        sql = sql + " ,x.fsite_name, p.fpoint_name, 2 fprocflag,g.equipment_id fmacno, v.fequipmenttag"
        sql = sql + " from ty_reported_data g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.equipment_id = v.fmacno"
        sql = sql + " and g.status in('8','6')"
        sql = sql + " and g.devtype in ('3','2')"
        sql = sql + " UNION"
        sql = sql + " select 6 fwarringtype,g.id fwarringid, g.fgetdate fwarringtime"
        sql = sql + " ,case g.devtype when '1' then '门磁探测器报警' when '2' then '水浸报警' when '3' then '烟感报警' when '4' then '燃气报警'"
        sql = sql + " when '5' then '红外感应报警' when '7' then '紧急按钮报警' else '' end fwarringnote"
        sql = sql + " ,x.fsite_name, p.fpoint_name, 2 fprocflag,g.equipment_id fmacno, v.fequipmenttag"
        sql = sql + " from ty_reported_data g, a_ts_site x, a_ts_point p,s_tc_equipment_inv v"
        sql = sql + " where 1=1"
        if query.fsiteNo is not None:
            sql = sql + " and x.fsite_no=:fsiteNo "
        if query.fpointNo is not None:
            sql = sql + " and p.fpointNo= :fpointNo "
        if query.ftakedateStart is not None:
            sql = sql + " and to_char(g.fgetdate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd"
        sql = sql + " and v.fsite_no=x.fsite_no and v.fpoint_no=p.fpoint_no"
        sql = sql + " and g.equipment_id = v.fmacno"
        sql = sql + " and g.status = '8'"
        sql = sql + " and g.devtype in ('5')"
        sql = sql + " order by fwarringtime desc"

    data_2000 = db.execute(text(sql), param).fetchall()

    # 计算总笔数+总页码
    counts = len(data_2000)
    page_num, a = divmod(int(counts), int(query.offset))
    if a > 0:
        page_num += 1
    info_dict = {}
    info_dict['total'] = counts
    info_dict['pages'] = (counts + query.limit - 1) // query.limit
    info_dict['limit'] = query.offset
    info_dict['page'] = query.pageIndex

    # 取出指定页码数据
    start = (int(query.pageIndex) - 1) * int(query.offset)
    end = int(query.pageIndex) * int(query.offset)
    data = data_2000[start:end]

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fwarringtype'] = data[key][0]
        d1['fwarringid'] = data[key][1]
        current_datetime = data[key][2].strftime('%Y-%m-%dT%H:%M:%S')
        d1['fwarringtime'] = current_datetime
        d1['fwarringnote'] = data[key][3]
        d1['flevelscre'] = None
        d1['fsiteName'] = data[key][4]
        d1['fpointName'] = data[key][5]
        d1['fprocflag'] = data[key][6]
        d1['fmacno'] = data[key][7]
        d1['fequipmenttag'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
